package com.bkhc.order.utils;

import com.bkhc.common.vo.GoodsSpecVo;
import com.bkhc.order.entity.OrderItem;
import com.bkhc.order.repository.ProductRepository;
import com.microsoft.schemas.office.visio.x2012.main.CellType;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;

public class DownExcelUtil {
    private static XSSFWorkbook workbook;
    private static XSSFCellStyle setBorder;
    private static XSSFCellStyle setBorderAndFont;
    private DownExcelUtil(){

    }
    public static DownExcelUtil newInstance(String path){

        try {
            File file = new File(path);
            InputStream inputStream = new FileInputStream(file);
            workbook = new XSSFWorkbook(inputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }

        setBorder = workbook.createCellStyle();
        setBorder.setBorderBottom(BorderStyle.THIN);
        setBorder.setBorderLeft(BorderStyle.THIN);
        setBorder.setBorderTop(BorderStyle.THIN);
        setBorder.setBorderRight(BorderStyle.THIN);
        setBorder.setAlignment(HorizontalAlignment.CENTER);

        setBorderAndFont = workbook.createCellStyle();
        setBorderAndFont.setBorderBottom(BorderStyle.THIN);
        setBorderAndFont.setBorderLeft(BorderStyle.THIN);
        setBorderAndFont.setBorderTop(BorderStyle.THIN);
        setBorderAndFont.setBorderRight(BorderStyle.THIN);
        setBorderAndFont.setAlignment(HorizontalAlignment.CENTER);
        Font fontStyle = workbook.createFont();
        fontStyle.setBold(true);
        fontStyle.setFontName("黑体");
        fontStyle.setFontHeightInPoints((short) 11);
        setBorderAndFont.setFont(fontStyle);
        return new DownExcelUtil();
    }

    public static void downLoadExcel(List<OrderItem> ois, List<GoodsSpecVo> goodsSpecList, HttpServletResponse response){
        XSSFSheet sheet = workbook.getSheet("Sheet1");
        sheet.setForceFormulaRecalculation(true);
        List title = new ArrayList();
        title.add("品牌");
        title.add("产品名称");
        for(GoodsSpecVo goodsSpecVo:goodsSpecList){
            if(ois.get(0).getGoodsCode().equals(goodsSpecVo.getGoodsCode())){
                title.add(goodsSpecVo.getSpecName());
            }
        }
        title.add("数量");
        title.add("单价");
        title.add("合计");
        for (int i =0;i<title.size();i++){
            XSSFRow row = sheet.getRow(0);
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(title.get(i).toString());
            cell.setCellStyle(setBorder);
        }
        int rowNo = 1;
        BigDecimal sum = new BigDecimal(0);
        for (OrderItem o:ois) {
            sheet.createRow(rowNo);
            XSSFRow row = sheet.getRow(rowNo);
            for(int i = 0; i < title.size() ; i++){
                row.createCell(i);
                XSSFCell cell = row.getCell(i);
                cell.setCellStyle(setBorder);
                if(i==0){
                    cell.setCellValue(o.getBrandName());
                }else if(i==1){
                    cell.setCellValue(o.getProductName());
                }else if(i==2){
                    for (GoodsSpecVo goodsSpecVo :goodsSpecList) {
                        if(goodsSpecVo.getGoodsCode().equals(o.getGoodsCode())){
                            if(i > 2){
                                row.createCell(i);
                                XSSFCell goodsCell = row.getCell(i);
                                goodsCell.setCellStyle(setBorder);
                                goodsCell.setCellValue(goodsSpecVo.getSpecValue());
                                i++;
                            }else {
                                cell.setCellValue(goodsSpecVo.getSpecValue());
                                i++;
                            }
                        }
                    }
                    i--;
                }else if (i == title.size()-3){
                    cell.setCellValue(o.getQuantity());
                }else if (i == title.size()-2){
                    cell.setCellValue(o.getPrice());
                }else if (i == title.size()-1){
                    cell.setCellValue(o.getTotal());
                    sum = sum.add(new BigDecimal(o.getTotal()));
                }
            }
            rowNo++;
        }
        CellRangeAddress total=new CellRangeAddress(ois.size()+1, ois.size()+1, 0, title.size()/2);
        CellRangeAddress price =new CellRangeAddress(ois.size()+1, ois.size()+1, title.size()/2+1, title.size()-1);
        sheet.addMergedRegion(total);
        sheet.addMergedRegion(price);
        XSSFRow row = sheet.createRow(ois.size()+1);
        XSSFCell totalCell = row.createCell(0);
        XSSFCell priceCell = row.createCell(title.size()/2+1);
        totalCell.setCellValue("合计");
        priceCell.setCellValue(sum.setScale(2, RoundingMode.HALF_UP).toString());
        for(int i = 0 ; i < title.size();i++){
            XSSFCell cell = row.getCell(i);
            if(cell == null){
                cell = row.createCell(i);
            }
            if(i < title.size()/2+1){
                cell.setCellStyle(setBorderAndFont);
            }else {
                cell.setCellStyle(setBorder);
            }

        }
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment; filename=" + UUID.randomUUID() + ".xlsx");
            ServletOutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        Runtime.getRuntime().gc();
    }
}
